﻿using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using OOXMLGenerator.Utils;
using OOXMLGenerator.Enums;
using System.Collections.Generic;
using OOXMLGenerator.DataStructure;
using OOXMLGenerator.Generator.Excel;

namespace OOXMLGenerator.Report
{
    /// <summary>
    /// Static class that provides method to generate an excel stream based on MS Office Excel template.
    /// </summary>
    public static class ExcelReportGenerator
    {
        #region public methods ....

        /// <summary>
        /// Creates a data structure of Excel content based on received parameters and generates a stream 
        /// with content filled into right Excel cells.
        /// </summary>
        /// <param name="innovator">Aras.IOM.Innovator - used to obtain data from DB Innovator</param>
        /// <param name="id">Part id</param>
        /// <param name="image">Part image</param>
        /// <param name="errorString">Empty if no error occurs; otherwise error message</param>
        /// <param name="stream">Excel pachage stream</param>
        /// <param name="contentList"></param>
        /// <param name="append"></param>
        /// <returns>   true - if stream was generated with no errors
        ///             false - otherwise</returns>

        public static bool Generate(Aras.IOM.Innovator innovator, string id, Image image, ref string errorString, out Stream stream,
            List<SectionContent> contentList, bool append)
        {
            bool ret = true;
            stream = new MemoryStream();
            try
            {
                DataStructure.DataStructure partDataStructure = FillDataStructure(innovator, id, contentList, append);
                partDataStructure.Header.Logo = image;

                OOXmlExcelGenerator gc = new OOXmlExcelGenerator();

                //set base format
                ExcelFormat ft = new ExcelFormat();
                ft.ApplyBackground = true;
                ft.BackgroundColor = "FFFF0000";
                ft.ApplyFont = true;
                ft.Font = "Corbel";
                ft.FontColor = "FFFFFFFF";
                ft.FontSize = 11D;

                List<ExcelElement> elements = new List<ExcelElement>();

                int row = 1;

                //create Header
                row = Header(row, ft, partDataStructure.Header, elements);
                SectionContent sectionContent;

                string lastColumnLetter = "I";
                for (int i = 0; i < partDataStructure.Content.Count; i++)
                {
                    sectionContent = partDataStructure.Content[i];
                    switch (sectionContent.Type)
                    {
                        case PartContentType.BillOfMaterials:
                            { //create BOMHeader
                                row = BOMHeader(row, lastColumnLetter, sectionContent, ft, elements);
                                row = BOMRow(row, lastColumnLetter, sectionContent, ft, elements);
                            } break;
                        case PartContentType.Documents:
                            {
                                row = DocumentsHeader(row, lastColumnLetter, sectionContent, ft, elements);
                                row = DocumentsRow(row, lastColumnLetter, sectionContent, ft, elements);
                            } break;
                        case PartContentType.ChangeRequests:
                            {
                                row = ChangeRequestsHeader(row, lastColumnLetter, sectionContent, ft, elements);
                                row = ChangeRequestsRow(row, lastColumnLetter, sectionContent, ft, elements);
                            } break;
                        case PartContentType.ECOHistory:
                            { //create ECOHistory
                                row = ECOHistoryHeader(row, lastColumnLetter, sectionContent, ft, elements);
                                row = ECOHistoryRow(row, lastColumnLetter, sectionContent, ft, elements);
                            } break;
                    }
                }

                stream = gc.CreatePackage(elements);
            }
            catch (Exception ex)
            {
                ret = false;
                errorString = ex.Message;
            }
            return ret;
        }

        #endregion

        #region private methods ....

        /// <summary>
        /// Fill in a DataStructure object with part attributes, BOM, Documents, Change Requests and ECO History.
        /// </summary>
        /// <param name="innovator"></param>
        /// <param name="id"></param>
        /// <param name="contentList"></param>
        /// <param name="append"></param>
        /// <returns></returns>
        private static DataStructure.DataStructure FillDataStructure(Aras.IOM.Innovator innovator, string id, List<SectionContent> contentList, bool append)
        {
            DataStructure.DataStructure partDataStructure = new DataStructure.DataStructure();
            //List<string> partAttributesNames = Enum.GetNames(typeof(PartAttributes)).ToList();
            //List<string> partAttributes = (from attrs in partAttributesNames
            //                               select attrs).ToList();
            List<string> partAttributes = Enum.GetNames(typeof(PartAttributes)).ToList();

            partDataStructure.Header.Attributes = IOMUtil.GetPartAttributes(innovator, partAttributes, id);

            if (contentList == null || append)
            {
                SectionContent scBOM = new SectionContent(Properties.Resources.Header_BillOfMaterials, IOMUtil.GetBOM(innovator, id), PartContentType.BillOfMaterials);
                partDataStructure.Content.Add(scBOM);
                Append(GetByType(PartContentType.BillOfMaterials, contentList), partDataStructure.Content);

                SectionContent scDocuments = new SectionContent(Properties.Resources.Header_Documents, IOMUtil.GetDocuments(innovator, id), PartContentType.Documents);
                partDataStructure.Content.Add(scDocuments);
                Append(GetByType(PartContentType.Documents, contentList), partDataStructure.Content);

                //SectionContent scChangeRequests = new SectionContent(Properties.Resources.Header_ChangeRequests, IOMUtil.GetChangeRequests(innovator, id), PartContentType.ChangeRequests);
                //partDataStructure.Content.Add(scChangeRequests);
                Append(GetByType(PartContentType.ChangeRequests, contentList), partDataStructure.Content);

                SectionContent scECOHistory = new SectionContent(Properties.Resources.Header_ECOHistory, IOMUtil.GetECOHistory(innovator, id), PartContentType.ECOHistory);
                partDataStructure.Content.Add(scECOHistory);
                Append(GetByType(PartContentType.ECOHistory, contentList), partDataStructure.Content);
            }
            else
            {
                if (contentList != null)
                {
                    for (int i = 0; i < contentList.Count; i++)
                        partDataStructure.Content.Add(contentList[i]);
                }
            }

            return partDataStructure;
        }

        /// <summary>
        /// Creates Header
        /// </summary>
        /// <param name="row">Excel row number</param>
        /// <param name="ft">Excel base format</param>
        /// <param name="header">header structure</param>
        /// <param name="elements">elements list  - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int Header(int row, ExcelFormat ft, DataStructure.StructureHeader header, List<ExcelElement> elements)
        {
            ExcelFormat ft1 = ExcelUtil.FormatCompanyLogo();
            ft1.MergeCells = true;
            ft1.MergeCellReference = string.Format("A{0}:I{0}", row);

            elements.Add(new ExcelString(string.Format("A{0}", row), "Company\nLogo", ft1));

            ExcelFormat fta2 = ExcelUtil.FormatHeaderAttributes();
            fta2.MergeCells = true;
            fta2.MergeCellReference = string.Format("A{0}:B{0}", row + 1);

            List<string> partAttributesNames = new List<string>(){Properties.Resources.PartAttribute_PartNumber, 
                Properties.Resources.PartAttribute_Name, Properties.Resources.PartAttribute_Revision, 
                Properties.Resources.PartAttribute_ReleaseStatus, Properties.Resources.PartAttribute_State,
                Properties.Resources.PartAttribute_ReleaseDate, Properties.Resources.PartAttribute_Type, 
                Properties.Resources.PartAttribute_Unit, Properties.Resources.PartAttribute_Make_Buy};

            // first are added attributes labels
            for (int i = 0; i < partAttributesNames.Count; i++)
            {
                ExcelFormat fta = new ExcelFormat(fta2);
                //rowHeight
                if (partAttributesNames[i].CompareTo(Properties.Resources.PartAttribute_PartNumber) == 0 ||
                    partAttributesNames[i].CompareTo(Properties.Resources.PartAttribute_Name) == 0)
                {
                    fta.RowHeight = 30.75D;
                    fta.FontSize = 16D;
                }
                fta.MergeCellReference = string.Format("A{0}:B{0}", row + i + 1);
                elements.Add(new ExcelString(string.Format("A{0}", row + i + 1), partAttributesNames[i], fta));
            }

            //second are added attributes values
            if (header != null && header.Attributes != null)
            {
                List<string> partAttributesKeys = header.Attributes.Keys.ToList();

                PartAttributes partAttribute;
                for (int i = 0; i < header.Attributes.Count; i++)
                {
                    ExcelFormat ftc2 = new ExcelFormat(fta2);
                    ftc2.FontSize = 12D;
                    ftc2.HorizAlignment = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left;
                    ftc2.MergeCellReference = string.Format("C{0}:F{0}", row + i + 1);

                    partAttribute = (PartAttributes)Enum.Parse(typeof(PartAttributes), partAttributesKeys[i], true);
                    switch (partAttribute)
                    {
                        case PartAttributes.item_number:
                            {
                                ftc2.FontSize = 16D;
                                int itemNumber;
                                if (!string.IsNullOrEmpty(header.Attributes[partAttribute.ToString()]) && int.TryParse(header.Attributes[partAttribute.ToString()], out itemNumber))
                                    elements.Add(new ExcelIntNumber(string.Format("C{0}", row + i + 1), itemNumber, ftc2));
                                else
                                    elements.Add(new ExcelString(string.Format("C{0}", row + i + 1), header.Attributes[partAttribute.ToString()], ftc2));
                            } break;
                        case PartAttributes.name:
                            {
                                ftc2.FontSize = 16D;
                                elements.Add(new ExcelString(string.Format("C{0}", row + i + 1), header.Attributes[partAttribute.ToString()], ftc2));
                            } break;
                        case PartAttributes.release_date:
                            {
                                DateTime releaseDate;
                                if (!string.IsNullOrEmpty(header.Attributes[partAttribute.ToString()]) &&
                                    DateTime.TryParse(header.Attributes[partAttribute.ToString()], out releaseDate))
                                    elements.Add(new ExcelDateTime(string.Format("C{0}", row + i + 1), releaseDate, 14U, ftc2));
                                else
                                    elements.Add(new ExcelString(string.Format("C{0}", row + i + 1), header.Attributes[partAttribute.ToString()], ftc2));
                            } break;
                        default:
                            {
                                elements.Add(new ExcelString(string.Format("C{0}", row + i + 1), header.Attributes[partAttribute.ToString()], ftc2));
                            } break;
                    }
                    ExcelFormat ft2empty = new ExcelFormat(fta2);
                    ft2empty.MergeCells = false;
                    ft2empty.MergeCellReference = string.Empty;
                    elements.Add(new ExcelString(string.Format("G{0}", row + i + 1), "", ft2empty));
                    elements.Add(new ExcelString(string.Format("H{0}", row + i + 1), "", ft2empty));
                    elements.Add(new ExcelString(string.Format("I{0}", row + i + 1), "", ft2empty));
                }
            }

            //add part image
            string imagePart1Data = OOXMLGenerator.Utils.ImageUtil.ImageToString(header.Logo);
            if (imagePart1Data != "")
            {
                ExcelImage img = new ExcelImage(string.Format("E{0}", row), string.Format("H{0}", row + header.Attributes.Count - 1), imagePart1Data);
                img.Cell1ColumnOffset = "533400";
                img.Cell1RowOffset = "85725";
                img.Cell2ColumnOffset = "632450";
                img.Cell2RowOffset = "194300";
                elements.Add(img);
            }

            return row + header.Attributes.Count + 1;
        }

        /// <summary>
        /// Creates BOM Header
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int BOMHeader(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            DataTable dataTable = sc.Content;
            if (dataTable.Columns.Count > 0)
            {
                string firstColumnLetter = "A";

                ExcelFormat fta11 = new ExcelFormat(ft);
                fta11.MergeCells = true;
                fta11.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + lastColumnLetter + "{0}", row);
                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), sc.Header, fta11));

                ExcelFormat fta12 = new ExcelFormat(ft);
                fta12.BackgroundColor = "FF33CBCC";
                int columnNo = 0;
                PartBOM partBOM;

                row++;

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    partBOM = (PartBOM)Enum.Parse(typeof(PartBOM), dataTable.Columns[i].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partBOM)
                    {
                        case PartBOM.item_number:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;
                        case PartBOM.name:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 3;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;
                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), dataTable.Columns[i].Caption, fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates BOM row
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int BOMRow(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            ExcelFormat fta12 = new ExcelFormat(ft);
            fta12.FontColor = "FF0265CB";
            fta12.BackgroundColor = "FFCCFFFF";

            PartDataSet.PartBOMDataTable bomTable = (PartDataSet.PartBOMDataTable)sc.Content;
            string firstColumnLetter;
            PartBOM partBOM;
            int columnNo = 0;

            foreach (PartDataSet.PartBOMRow bomRow in bomTable.Rows)
            {
                columnNo = 0;
                for (int j = 0; j < bomTable.Columns.Count; j++)
                {
                    columnNo++;

                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    partBOM = (PartBOM)Enum.Parse(typeof(PartBOM), bomTable.Columns[j].ColumnName, true);

                    switch (partBOM)
                    {

                        case PartBOM.item_number:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                ftb12.ApplyAlignment = true;
                                ftb12.HorizAlignment = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left;
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), bomRow[j].ToString(), ftb12));
                            } break;
                        case PartBOM.name:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 3;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                ftb12.ApplyAlignment = true;
                                ftb12.HorizAlignment = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left;
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), bomRow[j].ToString(), ftb12));
                            } break;
                        case PartBOM.sort_order:
                            {
                                if (!bomRow.Issort_orderNull())
                                    elements.Add(new ExcelIntNumber(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), bomRow.sort_order, fta12));
                                else
                                    elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), "", fta12));
                            }
                            break;
                        case PartBOM.quantity:
                            {
                                if (!bomRow.IsquantityNull())
                                    elements.Add(new ExcelRealNumber(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), bomRow.quantity, fta12));
                                else
                                    elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), "", fta12));
                            }
                            break;
                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), bomRow[j].ToString(), fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates Documents Header
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int DocumentsHeader(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            DataTable dataTable = sc.Content;
            if (dataTable.Columns.Count > 0)
            {
                string firstColumnLetter = "A";

                ExcelFormat fta11 = new ExcelFormat(ft);
                fta11.MergeCells = true;
                fta11.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + lastColumnLetter + "{0}", row);
                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), sc.Header, fta11));

                ExcelFormat fta12 = new ExcelFormat(ft);
                fta12.BackgroundColor = "FF33CBCC";
                int columnNo = 0;
                PartDocuments partDocuments;

                row++;

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    partDocuments = (PartDocuments)Enum.Parse(typeof(PartDocuments), dataTable.Columns[i].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partDocuments)
                    {
                        case PartDocuments.related_id:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 5;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;

                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), dataTable.Columns[i].Caption, fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), "", fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates Documents row
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int DocumentsRow(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            ExcelFormat fta12 = new ExcelFormat(ft);
            fta12.FontColor = "FF0265CB";
            fta12.BackgroundColor = "FFCCFFFF";

            PartDataSet.PartDocumentsDataTable documentsTable = (PartDataSet.PartDocumentsDataTable)sc.Content;
            string firstColumnLetter;
            PartDocuments partDocuments;
            int columnNo = 0;

            foreach (PartDataSet.PartDocumentsRow documentRow in documentsTable.Rows)
            {
                columnNo = 0;
                for (int j = 0; j < documentsTable.Columns.Count; j++)
                {
                    partDocuments = (PartDocuments)Enum.Parse(typeof(PartDocuments), documentsTable.Columns[j].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partDocuments)
                    {
                        case PartDocuments.sort_order:
                            {
                                if (!documentRow.Issort_orderNull())
                                    elements.Add(new ExcelIntNumber(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), documentRow.sort_order, fta12));
                                else
                                    elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), string.Empty, fta12));
                            }
                            break;

                        case PartDocuments.related_id:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 5;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), documentRow[j].ToString(), ftb12));
                            } break;

                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), documentRow[j].ToString(), fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates Change Requests Header
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int ChangeRequestsHeader(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            DataTable dataTable = sc.Content;
            if (dataTable.Columns.Count > 0)
            {
                string firstColumnLetter = "A";

                ExcelFormat fta11 = new ExcelFormat(ft);
                fta11.MergeCells = true;
                fta11.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + lastColumnLetter + "{0}", row);
                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), sc.Header, fta11));

                ExcelFormat fta12 = new ExcelFormat(ft);
                fta12.BackgroundColor = "FF33CBCC";
                int columnNo = 0;
                PartChangeRequests partChangeRequests;

                row++;

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    partChangeRequests = (PartChangeRequests)Enum.Parse(typeof(PartChangeRequests), dataTable.Columns[i].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partChangeRequests)
                    {
                        case PartChangeRequests.requested:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;
                        case PartChangeRequests.description:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 3;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;

                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), dataTable.Columns[i].Caption, fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates Change Requests Row
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int ChangeRequestsRow(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            ExcelFormat fta12 = new ExcelFormat(ft);
            fta12.FontColor = "FF0265CB";
            fta12.BackgroundColor = "FFCCFFFF";

            PartDataSet.PartChangeRequestsDataTable changeRequestsTable = (PartDataSet.PartChangeRequestsDataTable)sc.Content;
            string firstColumnLetter;
            PartChangeRequests partChangeRequests;
            int columnNo = 0;

            foreach (PartDataSet.PartChangeRequestsRow changeRequestRow in changeRequestsTable.Rows)
            {
                columnNo = 0;
                for (int j = 0; j < changeRequestsTable.Columns.Count; j++)
                {
                    partChangeRequests = (PartChangeRequests)Enum.Parse(typeof(PartChangeRequests), changeRequestsTable.Columns[j].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partChangeRequests)
                    {
                        case PartChangeRequests.requested:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), changeRequestRow[j].ToString(), ftb12));
                            } break;
                        case PartChangeRequests.description:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 3;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), changeRequestRow[j].ToString(), ftb12));
                            } break;
                        case PartChangeRequests.date:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                ftb12.ApplyAlignment = true;
                                ftb12.HorizAlignment = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left;
                                if (!changeRequestRow.IsdateNull())
                                    elements.Add(new ExcelDateTime(string.Format(firstColumnLetter + "{0}", row), changeRequestRow.date, 14U, ftb12));
                                else
                                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, ftb12));
                            } break;

                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), changeRequestRow[j].ToString(), fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }
            return row;
        }

        /// <summary>
        /// Creates ECO History Header
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int ECOHistoryHeader(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            DataTable dataTable = sc.Content;
            if (dataTable.Columns.Count > 0)
            {
                string firstColumnLetter = "A";
                ExcelFormat fta11 = new ExcelFormat(ft);
                fta11.MergeCells = true;
                fta11.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + lastColumnLetter + "{0}", row);
                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), sc.Header, fta11));

                ExcelFormat fta12 = new ExcelFormat(ft);
                fta12.BackgroundColor = "FF33CBCC";

                row++;

                int columnNo = 0;
                PartECOHistory partECO;

                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    partECO = (PartECOHistory)Enum.Parse(typeof(PartECOHistory), dataTable.Columns[i].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partECO)
                    {
                        case PartECOHistory.description:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 4;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;
                        case PartECOHistory.release_date:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), dataTable.Columns[i].Caption, ftb12));
                            } break;
                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), dataTable.Columns[i].Caption, fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;

                //ExcelFormat ftaI1 = new ExcelFormat(ft);
                //ftaI1.BackgroundColor = "FFCCFFFF";
                //elements.Add(new ExcelString(string.Format("I{0}", row + 1), "", ftaI1));
            }
            return row;
        }

        /// <summary>
        /// Creates ECO History Row
        /// </summary>
        /// <param name="row">start row number</param>
        /// <param name="lastColumnLetter"> the superior column limit</param>
        /// <param name="sc">section conten data</param>
        /// <param name="ft">base excel format</param>
        /// <param name="elements">elements list - here are added all new excel elements</param>
        /// <returns>the next row number</returns>
        private static int ECOHistoryRow(int row, string lastColumnLetter, SectionContent sc, ExcelFormat ft, List<ExcelElement> elements)
        {
            ExcelFormat fta12 = new ExcelFormat(ft);
            fta12.FontColor = "FF0265CB";
            fta12.BackgroundColor = "FFCCFFFF";

            PartDataSet.PartECOHistoryDataTable ecoTable = (PartDataSet.PartECOHistoryDataTable)sc.Content;
            string firstColumnLetter;
            PartECOHistory partECO;
            int columnNo = 0;

            foreach (PartDataSet.PartECOHistoryRow ecoRow in ecoTable.Rows)
            {
                columnNo = 0;
                for (int j = 0; j < ecoTable.Columns.Count; j++)
                {
                    partECO = (PartECOHistory)Enum.Parse(typeof(PartECOHistory), ecoTable.Columns[j].ColumnName, true);
                    ExcelFormat ftb12 = new ExcelFormat(fta12);
                    columnNo++;
                    switch (partECO)
                    {
                        case PartECOHistory.description:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo += 4;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), ecoRow[j].ToString(), ftb12));
                            } break;
                        case PartECOHistory.release_date:
                            {
                                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                                columnNo++;
                                ftb12.MergeCells = true;
                                ftb12.MergeCellReference = string.Format(firstColumnLetter + "{0}:" + ExcelUtil.GetColumnLetter(columnNo) + "{0}", row);
                                ftb12.ApplyAlignment = true;
                                ftb12.HorizAlignment = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left;
                                if (!ecoRow.Isrelease_dateNull())
                                    elements.Add(new ExcelDateTime(string.Format(firstColumnLetter + "{0}", row), ecoRow.release_date, 14U, ftb12));
                                else
                                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, ftb12));
                            } break;
                        default:
                            {
                                elements.Add(new ExcelString(string.Format(ExcelUtil.GetColumnLetter(columnNo) + "{0}", row), ecoRow[j].ToString(), fta12));
                            } break;
                    }
                }
                firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                while (firstColumnLetter.CompareTo(lastColumnLetter) < 0)
                {
                    columnNo++;
                    firstColumnLetter = ExcelUtil.GetColumnLetter(columnNo);
                    elements.Add(new ExcelString(string.Format(firstColumnLetter + "{0}", row), string.Empty, fta12));
                }
                row++;
            }

            return row;
        }

        /// <summary>
        /// Adds a pair (key, value) to given dictionary if the key is not already containt in dictionary.
        /// </summary>
        /// <param name="dictionary">dictionary where the tuple (key, value) has to be added</param>
        /// <param name="key">key</param>
        /// <param name="value">value</param>
        private static void Add(Dictionary<string, int> dictionary, string key, int value)
        {
            try
            {
                if (!dictionary.ContainsKey(key))
                    dictionary.Add(key, value);
            }
            catch (Exception ex) { throw ex; }
        }

        /// <summary>
        /// It adds for each column of section content data table a number that represents how may columns has to be merged
        /// for that column.
        /// </summary>
        /// <param name="sc">SectionContent parameter</param>
        /// <returns>a dictionary with columns length (how many columns has to be merged) for given section</returns>
        private static Dictionary<string, int> GetColumnsLength(SectionContent sc)
        {
            Dictionary<string, int> result = new Dictionary<string, int>();
            DataTable dataTable = sc.Content;
            string columnName = string.Empty;
            switch (sc.Type)
            {
                case PartContentType.BillOfMaterials:
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            columnName = dataTable.Columns[i].ColumnName;
                            switch ((PartBOM)Enum.Parse(typeof(PartBOM), columnName, true))
                            {
                                case PartBOM.item_number: Add(result, columnName, 1); break;
                                case PartBOM.name: Add(result, columnName, 3); break;
                                default: Add(result, columnName, 0); break;
                            }
                        }
                    } break;
                case PartContentType.Documents:
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            columnName = dataTable.Columns[i].ColumnName;
                            switch ((PartDocuments)Enum.Parse(typeof(PartDocuments), columnName, true))
                            {
                                case PartDocuments.related_id: Add(result, columnName, 5); break;
                                default: Add(result, columnName, 0); break;
                            }
                        }
                    } break;
                case PartContentType.ChangeRequests:
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            columnName = dataTable.Columns[i].ColumnName;
                            switch ((PartChangeRequests)Enum.Parse(typeof(PartChangeRequests), columnName, true))
                            {
                                case PartChangeRequests.requested: Add(result, columnName, 2); break;
                                case PartChangeRequests.description: Add(result, columnName, 3); break;
                                default: Add(result, columnName, 0); break;
                            }
                        }
                    }
                    break;
                case PartContentType.ECOHistory:
                    {
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            columnName = dataTable.Columns[i].ColumnName;
                            switch ((PartECOHistory)Enum.Parse(typeof(PartECOHistory), columnName, true))
                            {
                                case PartECOHistory.description: Add(result, columnName, 4); break;
                                case PartECOHistory.release_date: Add(result, columnName, 1); break;
                                default: Add(result, columnName, 0); break;
                            }
                        }
                    }
                    break;
            }
            return result;
        }

        /// <summary>
        /// Gets a list of SectionContent objects with Type equal to given PartContentType parameter.
        /// </summary>
        /// <param name="type">PartContentType parameter</param>
        /// <param name="sourceList">source SectionContent List that has to be filtered</param>
        /// <returns></returns>
        private static List<SectionContent> GetByType(PartContentType type, List<SectionContent> sourceList)
        {
            List<SectionContent> filteredList = new List<SectionContent>();
            if (sourceList != null)
            {
                for (int i = 0; i < sourceList.Count; i++)
                    if (sourceList[i].Type == type)
                        filteredList.Add(sourceList[i]);
            }
            return filteredList;
        }

        /// <summary>
        /// Appends SectionContent elements of source list to destination list
        /// </summary>
        /// <param name="sourceList"> source list</param>
        /// <param name="destinationList">destination list</param>
        private static void Append(List<SectionContent> sourceList, List<SectionContent> destinationList)
        {
            if (destinationList == null) destinationList = new List<SectionContent>();
            if (sourceList != null)
            {
                for (int i = 0; i < sourceList.Count; i++)
                    destinationList.Add(sourceList[i]);
            }
        }

        #endregion
    }
}
